﻿create table Project
(
	ID int primary key,
	Name nvarchar(100) not null,
	TypeCode int not null,
	Budget money not null default 0,
	CreatorID int not null,
	CreatorName nvarchar(20),
	PMID int,
	PMName nvarchar(20),
	InnerMembers nvarchar(500),
	OuterMembers nvarchar(500),
	StartTime datetime,
	EndTime datetime,
	Importance int not null default 0,
	Status int not null,
	Remark nvarchar(1000),
	Orders int not null default 0,
)
go
create table PrjMember
(
	PrjID int foreign key references Project(ID) on delete cascade,
	UserID int foreign key references [User](ID) on delete cascade,
)

go
create table PrjTask
(
	ID int primary key,
	Name nvarchar(100),
	PrjID int foreign key references Project(ID) on delete cascade,
	UpperID int not null,
	Depth int not null,
	Path varchar(100) not null,
	Orders int not null,
	UserIDs varchar(500),
	UserNames nvarchar(500),
	CreatorID int not null,
	CreatorName nvarchar(20),
	StartTime datetime,
	EndTime datetime,
	TimeLimit float,
	Workload int,
	IsPrediction bit,	
	[Percent] int,
	IsMilestone bit not null default 0,
	Remark nvarchar(1000),
	CalendarID int,
)
go
create table PrjTaskChange
(
	ID int primary key,
	TaskID int not null foreign key references PrjTask(ID) on delete cascade,
	UserID int not null,
	UserName nvarchar(20) not null,
	InputTime datetime not null default getdate(),
	Changes nvarchar(500), 
	Reason nvarchar(500),
)
go
create table PrjTaskSchedule
(
	ID int primary key,
	TaskID int not null foreign key references PrjTask(ID) on delete cascade,
	UserID int not null,
	UserName nvarchar(20) not null,
	InputTime datetime not null default getdate(),
	Workload int,
	[Percent] int,
	Remark nvarchar(500), 
)

go
create table PrjTaskOversee
(
	ID int primary key,
	TaskID int not null foreign key references PrjTask(ID) on delete cascade,
	UserID int not null,
	UserName nvarchar(20) not null,
	InputTime datetime not null default getdate(),
	Remark nvarchar(500), 
)
go
create table PrjTaskAttach
(
	ID		   int primary key,
	[Name]	   varchar(200) not null,
	BindID	   int foreign key references PrjTask(ID) on delete cascade,
	Path	   varchar(100),
	Type	   varchar(100),
	[Size]	   int not null,	
	InputTime datetime default getdate(),	
	Flag	int,
	CreatorID int,
	CreatorName nvarchar(20),
)

create table PrjBug
(
	ID int primary key,
	[Name] nvarchar(500) not null,
	PrjID int foreign key references Project(ID) on delete cascade,
	TypeCode int,
	Severity int,
	Priority int,
	PlanEndTime datetime,
	RealEndTime datetime,
	Remark nvarchar(500),
	Status int,
	CreatorID int ,
	CreatorName nvarchar(10),
	InputTime datetime,
	ReceiveID int ,
	ReceiveName nvarchar(10),	
	Feedback nvarchar(500),
)

create table [PrjBugPicture]
(
	[ID] [int] primary key NOT NULL,
	[Name] [varchar](100) NOT NULL,
	[BindID] [int] foreign key references [dbo].PrjBug([ID]) on delete cascade NOT NULL,
	[Remark] [nvarchar](500) NULL,
	[Flag] [varchar](50) NULL,
	[Size] [int] NOT NULL,
	[Width] [int] NOT NULL,
	[Height] [int] NOT NULL,
	[InputTime] [datetime] NOT NULL,
	SmallImage [varchar](500) NULL,
	BigImage [varchar](500) NULL,
	OriginImage [varchar](500) NULL
)
